/// Author: AustralTek 
/// Create date: 2010/05/04
/// http://www.australtek.com
/// Copyright: 2009 - 2012

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.IO;
using System.Reflection;
using System.ServiceModel;
using System.ServiceModel.Channels;
using System.ServiceModel.Web;
using System.Text;
using System.Web.Script.Serialization;
using WidgetInterface;
using System.Net;
using System.Web;
using System.Web.Security;
//using DALServiceData;
//using System.Security;

//[assembly: AllowPartiallyTrustedCallers]

/* This interface is used by javascript widgets */
[ServiceContract]
public interface IDALService
{   
    [OperationContract, WebInvoke(UriTemplate = "Login")]
    Stream Login(Stream Parameters);

    [OperationContract, WebInvoke(UriTemplate = "GetCookie")]
    Stream GetCookie();

    [OperationContract, WebInvoke(UriTemplate = "Logoff")]
    Stream Logoff();

    [OperationContract, WebInvoke(UriTemplate = "IsLogged")]
    Stream IsLogged();

    /// Return Object because it returns different classes for different widgets
    [OperationContract, WebInvoke(UriTemplate = "CallProcedure")]
    Stream CallProcedure(Stream Parameters);

    [OperationContract, WebInvoke(UriTemplate = "CallSecuredProcedure")]
    Stream CallSecuredProcedure(Stream Parameters);

    [OperationContract, WebInvoke(UriTemplate = "GetFile")]
    Stream GetFile(Stream Parameters);

    // Export objects
    [OperationContract, WebInvoke(UriTemplate = "ExportTo")]
    Stream ExportTo(Stream Parameters);

    [OperationContract, WebInvoke(UriTemplate = "AttachConnection")]
    Stream AttachConnection(Stream Parameters);
}

[System.ServiceModel.Activation.AspNetCompatibilityRequirements(RequirementsMode = System.ServiceModel.Activation.AspNetCompatibilityRequirementsMode.Allowed)]
public partial class DALService : IDALService
{
    private static Int32 ExpiresMinutesTime = 1740;
    //private List<Log> Logs = null;

    public static class UserConnections
    {
        private static Dictionary<String, DALServiceData.UserConnections> StaticUserConnections = new Dictionary<String, DALServiceData.UserConnections> { };
        public static void Add(String Key, DALServiceData.UserConnections InternalUser)
        {
            LogHelper.Log.LoggerName = "";
            LogHelper.Log.DisplayMethods = true;            
            try
            {
                //LogHelper.Log.Debug("     -UserConnections.Add [" + str + "]");
                ReleaseResources();
                lock (StaticUserConnections)
                {                   
                    if (!(StaticUserConnections.ContainsKey(Key)))
                    {
                        //LogHelper.Log.Debug("          -UserConnections.Added [" + str + "]");
                        StaticUserConnections.Add(Key, InternalUser);
                    }
                    String logSession = "";
                    Int16 SessionCount = 0;
                    foreach (KeyValuePair<String, DALServiceData.UserConnections> kvp in StaticUserConnections)
                    {
                        logSession += "{Key: " + kvp.Key + ", User: " + ((DALServiceData.UserConnections)kvp.Value).UserName + "}, ";
                        SessionCount++;
                    }
                    if (SessionCount > 0)
                    {
                        logSession = logSession.Substring(0, logSession.Length - 2);
                    }
                    LogHelper.Log.Debug("          -Current Users: [Count: " + SessionCount + ", User:[" + logSession + "]]");
                }
            }
            catch (Exception ex)
            {
                String str = DALService.GetUserStringLog(InternalUser);
                LogHelper.Log.Debug("          -UserConnections. Failed. [" + str + "]");
                LogHelper.Log.Error("UserConnections. Failed. [" + str + "]", ex);
                throw new Exception("UserConnections. Cannot add UserConnection to Dictionary. ", ex);
            }
        }
        public static Boolean Update(DALServiceData.UserConnections uc)
        {
            LogHelper.Log.LoggerName = "";
            LogHelper.Log.DisplayMethods = true;            
            //LogHelper.Log.Debug("     -Update UserConnections [" + str + "]");               
            try
            {
                Boolean ret = false;
                lock (StaticUserConnections)
                {
                    if (StaticUserConnections.ContainsKey(uc.Key))
                    {
                        //LogHelper.Log.Debug("          -Update UserConnection. [" + str + "]");
                        //LogHelper.Log.Debug("          -Update Remove UserConnection. [" + str + "]");
                        StaticUserConnections.Remove(uc.Key);
                        //LogHelper.Log.Debug("          -Update Add UserConnection. [" + str + "]");
                        StaticUserConnections.Add(uc.Key, uc);
                        ret= true;
                    }
                }
                return ret;
            }
            catch (Exception ex)
            {
                String str = DALService.GetUserStringLog(uc);
                LogHelper.Log.Debug("          -Update UserConnections Failed. [" + str + "]");
                LogHelper.Log.Error("Update UserConnections Failed. [" + str + "]", ex);
                throw new Exception("Update UserConnections. Cannot update UserConnection. ", ex);
            }
        }
        public static void Remove(String Key)
        {
            LogHelper.Log.LoggerName = "";
            LogHelper.Log.DisplayMethods = true;
            LogHelper.Log.Debug("     -Remove UserConnections [Key: " + Key + "]");                          
            try
            {
                lock (StaticUserConnections)
                {
                    if (StaticUserConnections.ContainsKey(Key))
                    {
                        LogHelper.Log.Debug("          -Removed [Key: " + Key + "]");
                        StaticUserConnections.Remove(Key);
                    }
                }
            }
            catch (Exception ex)
            {
                LogHelper.Log.Debug("          -Remove Failed. [Key: " + Key + "]");
                LogHelper.Log.Error("Remove Failed. [Key: " + Key + "]", ex);
                throw new Exception("Remove Failed. Cannot remove UserConnection from Dictionary. ", ex);
            }
        }
        public static void ReleaseResources()
        {
            try
            {
                String[] Keys = {};
                Int16 size = 0;
                foreach(KeyValuePair<String,DALServiceData.UserConnections> kvp in StaticUserConnections)
                {
                    DALServiceData.UserConnections uc = kvp.Value;
                    if ((uc.LastContact.AddMinutes(ExpiresMinutesTime) < DateTime.UtcNow)&&(uc.Key!="NULL"))
                    {
                        Array.Resize(ref Keys,size+1);
                        Keys[size] = kvp.Key;
                        size++;
                    }
                }
                foreach (String key in Keys)
                {
                    Remove(key);
                }
            }
            catch (Exception ex)
            {
                LogHelper.Log.Debug("          -ReleaseResources Failed. ["+ex.Message+"]");
                LogHelper.Log.Error("ReleaseResources Failed.", ex);
                throw new Exception("ReleaseResources Failed.", ex);
            }
        }
        public static DALServiceData.UserConnections GetUserConnections(String Key)
        {
            try
            {
                LogHelper.Log.LoggerName = "";
                //LogHelper.Log.Info("     -Initializing GetUserConnections [Key: " + Key + "]");
                //LogHelper.Log.Debug("     -Initializing GetUserConnections [Key: " + Key + "]");
                //StaticUserConnections
                lock (StaticUserConnections)
                {
                    if ((StaticUserConnections != null) && (StaticUserConnections.ContainsKey(Key)))
                    {
                        //LogHelper.Log.Debug("          -Return UserConnections [Key: " + Key + "]");                    
                        return StaticUserConnections[Key];
                    }
                    else
                    {
                        //LogHelper.Log.Debug("          -Return (NULL)");
                        return null;
                    }
                }

            }
            catch (Exception ex)
            {                
                LogHelper.Log.Debug("          -GetUserConnections Failed. [Key: " + Key + "]");
                LogHelper.Log.Error("GetUserConnections Failed. [Key: " + Key + "]", ex);
                throw new Exception("GetUser Failed.", ex);
            }
        }
        public static DALServiceData.UserConnections GetUserConnections(String UserName, String Password)
        {
            try
            {
                LogHelper.Log.LoggerName = "";
                //LogHelper.Log.Info("     -Initializing GetUserConnections [UserName: "+ UserName +"]");
                //LogHelper.Log.Debug("     -Initializing GetUserConnections [UserName: " + UserName + "]");
                DALServiceData.UserConnections uc = null;
                if (StaticUserConnections != null)
                {
                    lock (StaticUserConnections)
                    {
                        foreach (KeyValuePair<String, DALServiceData.UserConnections> kvp in StaticUserConnections)
                        {
                            if ((kvp.Value.UserName == UserName) && (kvp.Value.Password == Password))
                            {
                                LogHelper.Log.Debug("          -Return UserConnections [UserName: " + UserName + "]");
                                uc = kvp.Value;
                                break;
                            }
                        }
                    }
                }
                if (uc == null) LogHelper.Log.Debug("          -Return (NULL)");
                else LogHelper.Log.Debug("          -Return UserConnections []");
                return uc;                
            }
            catch (Exception ex)
            {
                LogHelper.Log.Debug("          -GetUserConnections Failed. [UserName: " + UserName + "]");
                LogHelper.Log.Error("GetUserConnections Failed. [UserName: " + UserName + "]", ex);
                throw new Exception("GetUserConnections Failed. [UserName: " + UserName + "]", ex);
            }
        }
    }
   
    #region "Implements"
    ///<summary>
    ///
    ///</summary>   
    public Stream GetCookie()
    {       
        LogHelper.Log.LoggerName = "";
        LogHelper.Log.DisplayMethods = true;
        LogHelper.Log.Info("Initializing GetCookie");
        LogHelper.Log.Debug("Initializing GetCookie");
        JavaScriptSerializer jss = new JavaScriptSerializer();
        jss.MaxJsonLength = 2147483647;
        try
        {            
            DALServiceData.UserConnections uconn = GetLoggedUser();
            if ((uconn != null) && (uconn.Key.ToUpper() == "NULL")) return null;
            else if (uconn == null)return new MemoryStream(Encoding.UTF8.GetBytes(jss.Serialize(null)));  
            DALServiceData.User u = new DALServiceData.User();
            u.UserName = uconn.UserName;
            u.LoggedTime = uconn.Logged;
            foreach (KeyValuePair<String, DALServiceData.LocalSecurityData> kvp in uconn.SecurityData)
            {
                DALServiceData.LocalSecurityData lsd = kvp.Value;
                DALServiceData.SecurityData sd = new DALServiceData.SecurityData();
                sd.ExtendedProperties = lsd.ExtendedProperties;
                sd.Roles = lsd.Roles;
                u.SecurityData.Add(kvp.Key, sd);
            }
            LogHelper.Log.Debug("     -GetCookie. Return Cookie [UserName: " + u.UserName + "]");
            return new MemoryStream(Encoding.UTF8.GetBytes(jss.Serialize(u)));              
        }
        catch (Exception ex)
        {
            LogHelper.Log.Debug("     -GetCookie Failed.");
            LogHelper.Log.Error("GetCookie Failed.",ex);
            DALServiceData.ErrorStruct es = new DALServiceData.ErrorStruct();
            es.ErrorType = "GetCookie failed";
            es.Message = ex.Message;
            return new MemoryStream(Encoding.UTF8.GetBytes(jss.Serialize(es)));  
        }
    }
    private String GetClientCookie(String Key, DateTime date)
    {
        try
        {
            HttpCookie hc = new HttpCookie(Key);
            hc.Path = "";
            hc.Domain = "/IndustrialDashboard/";
            hc.Secure = true;
            hc.Value = "SessionID=" + Key;
            hc.ToString();
            String cookie = "SessionID=" + Key + ",expires=" + date.ToString() + " ,domain=IndustrialDashboard/, path=/"; //("ddd, d MMM yyyy hh:mm:ss GTM")
            LogHelper.Log.Debug("     -Cookie: " + cookie);
            return cookie;
        }
        catch (Exception ex)
        {
            LogHelper.Log.Debug("     -GetClientCookie. ["+ex.Message+"]");
            return "";
        }
    }
    /// <summary>
    /// 
    /// </summary>
    public Stream IsLogged()
    {
        LogHelper.Log.LoggerName = "";
        LogHelper.Log.DisplayMethods = true;
        LogHelper.Log.Info("Initializing IsLogged");
        LogHelper.Log.Debug("Initializing IsLogged");
        JavaScriptSerializer jss = new JavaScriptSerializer();
        jss.MaxJsonLength = 2147483647;
        try
        {
            ///////////////////////////////////////////
            Object cookie = WebOperationContext.Current.IncomingRequest.Headers[HttpRequestHeader.Cookie];
            String NameID = "SessionID";
            if (cookie != null)
            {
                String[] str = cookie.ToString().Split(';');
                for (Int32 i = 0; i < str.Length; i++)
                {
                    String s = str[i].Trim();
                    if ((s.Length >= NameID.Length) && (s.Substring(0, NameID.Length) == NameID))
                    {
                        String[] Attributes = s.Split(',');
                        String Key = "";
                        DateTime ExpireDate = DateTime.UtcNow.AddMinutes(-1);
                        for (Int32 k = 0; k < Attributes.Length; k++)
                        {
                            String[] Attribute = Attributes[k].Split('=');
                            switch (Attribute[0].ToUpper().Trim())
                            {
                                case "SESSIONID":
                                    Key = Attribute[1];
                                    break;
                                case "EXPIRES":
                                    if ((Key != "") && (UserConnections.GetUserConnections(Key) != null))
                                    {
                                        ExpireDate = Convert.ToDateTime(Attribute[1]);
                                    }
                                    break;
                            }
                        }
                        DALServiceData.UserConnections iu = UserConnections.GetUserConnections(Key);
                        if ((Key != "") && (ExpireDate > DateTime.UtcNow))
                        {
                            if (iu != null)
                            {
                                iu.LastContact = DateTime.UtcNow;
                                return new MemoryStream(Encoding.UTF8.GetBytes(jss.Serialize(true)));
                            }
                        }
                        else if ((Key != "") && (ExpireDate < DateTime.UtcNow))
                        {
                            if (iu != null)
                            {
                                LogHelper.Log.Debug("          -Session has expired [" + iu.UserName + "]");
                                //remove SessionID from Dictionary
                                UserConnections.Remove(Key);
                            }
                            else LogHelper.Log.Debug("          -Session has expired.");
                            return new MemoryStream(Encoding.UTF8.GetBytes(jss.Serialize(false)));
                        }
                    }
                }
            }
            ///////////////////////////////////////////            
            return new MemoryStream(Encoding.UTF8.GetBytes(jss.Serialize(false)));
        }
        catch (Exception ex)
        {
            LogHelper.Log.Debug("     -IsLogged Failed.");
            LogHelper.Log.Error("IsLogged Failed.", ex);
            DALServiceData.ErrorStruct es = new DALServiceData.ErrorStruct();
            es.ErrorType = "IsLogged failed";
            es.Message = ex.Message;
            return new MemoryStream(Encoding.UTF8.GetBytes(jss.Serialize(es)));
        }
    }
    /// <summary>
    /// 
    /// </summary>    
    public Stream Login(Stream Parameters)
    {
        LogHelper.Log.LoggerName = "";
        LogHelper.Log.DisplayMethods = true;
        LogHelper.Log.Info("Initializing Login");
        LogHelper.Log.Debug("Initializing Login"); 
        JavaScriptSerializer jss = new JavaScriptSerializer();
        jss.MaxJsonLength = 2147483647;
        //Boolean AllowCookies = false;
        try
        {
            //HttpApplication httpApp;
            //HttpCookie cookie = new HttpCookie("Test", "asdasdasd");
            //httpApp.Context.Response.AppendCookie(cookie);
            DALServiceData.LoginParameters lp;
            DateTime now = DateTime.UtcNow.AddMinutes(ExpiresMinutesTime);
            try
            {
                /* Get input parameters from input stream */
                StreamReader sr = new StreamReader(Parameters);
                lp = jss.Deserialize<DALServiceData.LoginParameters>(sr.ReadToEnd());
                if (lp == null)
                {
                    LogHelper.Log.Debug("     -Cannot Login. UserName and Password are nulls");
                    return new MemoryStream(Encoding.UTF8.GetBytes(jss.Serialize(null)));

                }
                //AllowCookies = (lp.AllowCookies != null) ? Convert.ToBoolean(lp.AllowCookies) : false;
                //LogHelper.Log.Debug("     -Login [UserName: " + lp.UserName + "]");
                DALServiceData.User u;
                //Remove from the memory previous user. 
                try
                {
                    if ((lp.UserName.Trim() == "")) return new MemoryStream(Encoding.UTF8.GetBytes(jss.Serialize(false))); 
                    DALServiceData.UserConnections uc = null;
                    uc = GetLoggedUser();
                    if ((uc != null) && (uc.Key != "NULL")&&(uc.UserName!=lp.UserName))
                    {
                        LogHelper.Log.Debug("     -Remove previous User [" + uc.UserName + "]");
                        LogHelper.Log.Info("     -Remove previous User [" + uc.UserName + "]");
                        UserConnections.Remove(uc.Key);
                    } else if ((uc!=null)&&(uc.UserName==lp.UserName)){                       
                        //Renovate cookie.
                        LogHelper.Log.Debug("     -The user is already logged [" + uc.UserName + "]");
                        LogHelper.Log.Info("     -The user is already logged [" + uc.UserName + "]");
                        u = new DALServiceData.User();
                        //if (AllowCookies == false)
                        //{                            
                            String strCookie = GetClientCookie(uc.Key, now);
                            WebOperationContext.Current.OutgoingResponse.Headers.Remove("Set-cookie");
                            WebOperationContext.Current.OutgoingResponse.Headers[HttpResponseHeader.SetCookie] = strCookie;
                        //}else u.Key = uc.Key;

                        u.UserName = uc.UserName;
                        u.LoggedTime = DateTime.UtcNow;
                        foreach (KeyValuePair<String, DALServiceData.LocalSecurityData> kvp in uc.SecurityData)
                        {
                            DALServiceData.LocalSecurityData lsd = kvp.Value;
                            DALServiceData.SecurityData sd = new DALServiceData.SecurityData();
                            sd.ExtendedProperties = lsd.ExtendedProperties;
                            sd.Roles = lsd.Roles;
                            u.SecurityData.Add(kvp.Key, sd);
                        }                        
                        return new MemoryStream(Encoding.UTF8.GetBytes(jss.Serialize(u)));
                    }
                    //else if ((uc != null) && (uc.Key != "NULL")&&(uc.UserName!=lp.UserName))
                }
                catch { }
                
                u = LoginUser(lp.UserName, lp.Password,true,null);
                if (u != null)
                {
                    u.LoggedTime = DateTime.UtcNow;
                    LogHelper.Log.Debug("     -Login Accepted. [UserName: " + lp.UserName + "]"); 
                    return new MemoryStream(Encoding.UTF8.GetBytes(jss.Serialize(u)));
                }
                else
                {
                    LogHelper.Log.Debug("     -Login Failed. [UserName: " + lp.UserName + "]");
                    return new MemoryStream(Encoding.UTF8.GetBytes(jss.Serialize(false)));
                }
            }
            catch (FormatException ex)
            {
                LogHelper.Log.Debug("     -Login Failed. FormatException [Message: "+ex.Message+"]");
                LogHelper.Log.Error("Login Failed. FormatException [Message: " + ex.Message + "]", ex);
                return null;
            }
        }
        catch (Exception ex)
        {            
            LogHelper.Log.Debug("     -Login Failed. [" + ex.Message+"]");            
            LogHelper.Log.Error("Login Failed.", ex);
            //Use ErrorStruct to return data;
            DALServiceData.ErrorStruct es = new DALServiceData.ErrorStruct();
            es.ErrorType = ex.InnerException.ToString();
            es.Message = ex.Message;                       
            //Release client cookie.
            DateTime now = DateTime.UtcNow;
            //if (AllowCookies==false)
            WebOperationContext.Current.OutgoingResponse.Headers.Remove("Set-Cookie");
            WebOperationContext.Current.OutgoingResponse.Headers.Add("Set-Cookie", "SessionID=''");            
            LogHelper.Log.Debug("     -Login Failed. Remove Cookie.");                      
            return new MemoryStream(Encoding.UTF8.GetBytes(jss.Serialize(es)));          
        }        
    }
    private DALServiceData.User LoginUser(String UserName, String Password,Boolean AllowCookie,DALServiceData.UserConnections uc)
    {
        DateTime now = DateTime.UtcNow.AddMinutes(ExpiresMinutesTime);
        try
        {
            LogHelper.Log.LoggerName = "";
            LogHelper.Log.DisplayMethods = true;
            LogHelper.Log.Info("     -Initializing LoginUser [UserName: " + UserName + ", AllowCookie: "+ AllowCookie+"]");
            LogHelper.Log.Debug("     -Initializing LoginUser [UserName: " + UserName + ", AllowCookie: " + AllowCookie + "]");
            DALServiceData.UserConnections uconn=null;
            if (WebOperationContext.Current.IncomingRequest.Headers[HttpRequestHeader.Cookie] != null)
            {
                WebOperationContext.Current.OutgoingResponse.Headers.Remove("Set-Cookie");//, "SessionID=''");
                WebOperationContext.Current.OutgoingResponse.Headers.Add("Set-Cookie", "SessionID=''");
            }
            if (uc!=null) uconn = uc;
            else uconn = GetUserData(UserName, Password);
            if (uconn == null)
            {
                WebOperationContext.Current.OutgoingResponse.Headers.Remove("Set-Cookie");
                WebOperationContext.Current.OutgoingResponse.Headers.Add("Set-Cookie", "SessionID=''");
                return null;
            }
            uconn.LastContact = uconn.Logged = DateTime.UtcNow;
            uconn.IPAddress = LogHelper.HostRequest.GetIPAddress();
            if (uconn != null)
            {
                if (AllowCookie)
                {
                    LogHelper.Log.Debug("          -LoginUser - Adding Cookie [UserName: " + UserName +"]");
                    String key = System.Guid.NewGuid().ToString();
                    String strCookie = GetClientCookie(key, now);
                    WebOperationContext.Current.OutgoingResponse.Headers.Remove("Set-Cookie");
                    WebOperationContext.Current.OutgoingResponse.Headers.Add("Set-Cookie", strCookie);
                    uconn.Key = key;
                    UserConnections.Add(key, uconn);
                }
                else
                {
                    uconn.Key = "NULL";
                    UserConnections.Add(uconn.Key, uconn);
                }               

                DALServiceData.User u = new DALServiceData.User();
                u.UserName = UserName;
                foreach (KeyValuePair<String, DALServiceData.LocalSecurityData> kvp in uconn.SecurityData)
                {
                    DALServiceData.LocalSecurityData lsd = kvp.Value;
                    DALServiceData.SecurityData sd = new DALServiceData.SecurityData();
                    sd.ExtendedProperties = lsd.ExtendedProperties;
                    sd.Roles = lsd.Roles;
                    u.SecurityData.Add(kvp.Key, sd);
                }
                LogHelper.Log.Debug("          -Return LoginUser [UserName: " + UserName + ", AllowCookie: " + AllowCookie + "]");
                if (AllowCookie == true)
                {
                    //write SessionID into cookie, allowing the client relogin without enter the password.
                    u.Key = uconn.Key.ToString();
                }
                else
                {
                    u.Key = null;
                }
                return u;
            }
            else
            {
                LogHelper.Log.Debug("          -Return LoginUser (NULL). [UserName: " + UserName + ", AllowCookie: " + AllowCookie + "]");
                return null;
            }
        }
        catch (Exception ex)
        {
            LogHelper.Log.Debug("          -LoginUser Failed. [UserName: " + UserName + ", AllowCookie: " + AllowCookie + "]");
            LogHelper.Log.Error("LoginUser Failed. [UserName: " + UserName + ", AllowCookie: " + AllowCookie + "]",ex);
            throw new Exception("LoginUser Failed. [UserName: " + UserName + ", AllowCookie: " + AllowCookie + "]",ex);
        }
    }
    ///<summary>
    ///
    ///</summary>
    public Stream Logoff()
    {
        LogHelper.Log.LoggerName = "";
        LogHelper.Log.DisplayMethods = true;
        LogHelper.Log.Info("Initializing Logoff");
        LogHelper.Log.Debug("Initializing Logoff");
        //DALServiceData.LoginParameters lp = null;
        JavaScriptSerializer jss = new JavaScriptSerializer();
        jss.MaxJsonLength = 2147483647;
        try
        {
            /* Get input parameters from input stream */                                       
            Object obj = WebOperationContext.Current.IncomingRequest.Headers["Cookie"];
            String NameID = "SessionID";
            if (obj != null)
            {
                String[] str = obj.ToString().Split(';');

                for (Int32 i = 0; i < str.Length; i++)
                {
                    String s = str[i].Trim();
                    if ((s.Length >= NameID.Length) && (s.Substring(0, NameID.Length) == NameID))
                    {
                        String pattern = "SessionID*=[a-z|A-Z|0-9-]*";
                        System.Text.RegularExpressions.Regex regex = new System.Text.RegularExpressions.Regex(pattern);
                        System.Text.RegularExpressions.MatchCollection mc = regex.Matches(s);
                        if ((mc != null) && (mc.Count > 0))
                        {
                            String Key = mc[0].ToString().Split('=')[1];
                            DALServiceData.UserConnections uc = UserConnections.GetUserConnections(Key);
                            if (uc != null)
                            {
                                UserConnections.Remove(Key);
                                LogHelper.Log.Debug("     -Logoff: " + uc.UserName);
                                LogHelper.Log.Info("     -Logoff: " + uc.UserName);
                            }
                        }
                        WebOperationContext.Current.OutgoingResponse.Headers.Remove("Set-Cookie");
                        WebOperationContext.Current.OutgoingResponse.Headers.Add("Set-Cookie", "SessionID=''");
                        LogHelper.Log.Debug("     -Logoff [Accepted]");
                        return new MemoryStream(Encoding.UTF8.GetBytes(jss.Serialize(true)));
                    }
                }
                LogHelper.Log.Debug("     -Logoff [False]");
                return new MemoryStream(Encoding.UTF8.GetBytes(jss.Serialize(false)));
            }
            else
            {
                LogHelper.Log.Debug("     -Logoff [False]");
                return new MemoryStream(Encoding.UTF8.GetBytes(jss.Serialize(false)));
            }
        }
        catch (Exception ex)
        {
            WebOperationContext.Current.OutgoingResponse.Headers.Remove("Set-Cookie");
            WebOperationContext.Current.OutgoingResponse.Headers.Add("Set-Cookie", "SessionID=''");
            LogHelper.Log.Debug("     -Logoff Failed.");
            LogHelper.Log.Error("Logoff Failed.", ex);
            throw new Exception("Logoff Failed.", ex);
        }
    }    
    /// <summary>
    ///  Execute an arbitrary stored procedure, with the provided parameters and returns
    ///  a json response, customized based on widget class, if no class is provided the resulting 
    ///  dataset is serialized as a list of tables.
    /// </summary>
    /// <param name="Parameters">receive a json serialized ProcedureParameters object</param>
    /// <returns></returns>
    public Stream CallProcedure(Stream Parameters)
    {               
        LogHelper.Log.LoggerName = "";
        LogHelper.Log.DisplayMethods = true;
        LogHelper.Log.Info("Initializing.");
        LogHelper.Log.Debug("Initializing.");
        DALServiceData.DataParameters dp;
        JavaScriptSerializer jss = new JavaScriptSerializer();
        jss.MaxJsonLength = 2147483647;
        try
        {
            /* Get input parameters from input stream */
            StreamReader sr = new StreamReader(Parameters);           
            dp = jss.Deserialize<DALServiceData.DataParameters>(sr.ReadToEnd());              
        }
        catch (FormatException ex)
        {
            LogHelper.Log.Warn("     -FormatException", ex);
            LogHelper.Log.Error("     -FormatException", ex);            
            return null;
        }
        try
        {
            if (dp == null)
            {
                LogHelper.Log.Debug("     -Nulls Parameters");
                throw new UserException("Nulls Parameters");
            }
            if (dp.DatabaseParameters == null)
            {
                LogHelper.Log.Debug("     -No DatabaseParameters specified");
                throw new UserException("No DatabaseParameters specified.");
            }
            else if (dp.DatabaseParameters.Procedure == null)
            {
                LogHelper.Log.Debug("     -No Procedure specified");
                throw new UserException("No Procedure specified.");
            }

            String strParameters = "";            
            strParameters += "Class : " + dp.Class;
            strParameters += ", Path : " + dp.Path;
            strParameters += ", Namespace : " + dp.Namespace;
            strParameters += ", FileName : " + dp.FileName;
            strParameters += ", Extension : " + dp.Extension;
            strParameters += ", DomainFolder : " + dp.DomainFolder;
            strParameters += ", ConnectionStringName : " + dp.DatabaseParameters.ConnectionStringName;
            strParameters += ", DBEngine : " + dp.DatabaseParameters.DBEngine;
            strParameters += ", Procedure : " + dp.DatabaseParameters.Procedure;
            strParameters += ", TablesCount : " + dp.DatabaseParameters.TablesCount;
            if ((dp.DatabaseParameters != null) && (dp.DatabaseParameters.QueryParameters != null) && (dp.DatabaseParameters.QueryParameters.Count > 0))
            {
                Int32 qpIndex = 1;
                strParameters += ", QueryParameters : {";
                foreach (DALServiceData.QueryParameter qp in dp.DatabaseParameters.QueryParameters)
                {
                    strParameters += "Param : " + qpIndex.ToString() + ", Name: " + qp.Name + ", Value:" + qp.Value + ", Type: " + qp.Type;
                    qpIndex++;
                }
                strParameters += "}";
            }
            LogHelper.Log.Debug("     -Parameters [" + strParameters + "]");


            //LogHelper.Log.Debug("     -CallProcedure. Executing Procedure. (Class: " + dp.Class + ", Procedure: " + dp.DatabaseParameters.Procedure + ")");
            /* Execute Procedure with named and typed parameters */
            DataSet ds = ExecuteProcedure(dp);
            if (ds == null)
            {
                LogHelper.Log.Debug("     -The dataset is null");
                throw new DataException("The dataset is null");
            }
            Object resultObject = null;            
            if (dp.Class != null)
            {
                /* Use Reflection to instatiate a "WidgetType" object and cast it as IWidget */
                Assembly assem = null;
                try
                {
                    String physicalPath = "";
                    if ((dp.Class == null) && (dp.Class == "")) dp.Class = "";
                    if ((dp.DomainFolder == null) || (dp.DomainFolder == "")) dp.DomainFolder = "Widgets\\" + dp.Class + "\\DLL";
                    physicalPath = ((dp.Path != null) && (dp.Path != "")) ? dp.Path : System.Web.Hosting.HostingEnvironment.ApplicationPhysicalPath + dp.DomainFolder+"\\";
                    physicalPath += ((dp.FileName != null) && (dp.FileName != "")) ? dp.FileName : dp.Class;
                    physicalPath += ((dp.Extension != null) && (dp.Extension != "")) ? "."+dp.Extension : ".dll"; 
                    //to prevent exception, check if the file exist before invoke
                    System.IO.FileInfo fi = new FileInfo(physicalPath);
                    if (fi.Exists == true)
                    {
                        assem = Assembly.LoadFrom(physicalPath);
                    }
                    else
                    {
                        LogHelper.Log.Debug("     -None DLL was found for Class: [" + dp.Class + "], trying to instantiate locally]. ");
                    }
                }
                catch (Exception ex)
                {
                    LogHelper.Log.Debug("     -None DLL was found for Class: [" + dp.Class + "], trying to instantiate locally]. " + ex.Message.ToString());
                    LogHelper.Log.Error("     -None DLL was found for Class: [" + dp.Class + "], trying to instantiate locally]. ",ex);
                }
                if (assem == null)
                {
                    assem = this.GetType().Assembly;
                }
                IWidget iWidgetObject = (IWidget)assem.CreateInstance(dp.Namespace + "." + dp.Class);                
                if (iWidgetObject == null)
                {
                    LogHelper.Log.Debug("No declared class was found for Class: [" + dp.Class + "]");
                    throw new Exception("No declared class was found for Class: [" + dp.Class + "]");
                }
                /* Send the DataSet to widget-custom class to convert result */
                try
                {
                    LogHelper.Log.Debug("     -Generating Response");
                    resultObject = iWidgetObject.GenerateResponse(ds, dp.Language);                
                }
                catch (UserException ue)
                {
                    LogHelper.Log.Debug("     -Failed. [Problems building the " + dp.Class + "]");
                    LogHelper.Log.Error("     -Failed. [Problems building the " + dp.Class + "]", ue);
                    throw new UserException("Problems building the " + dp.Class + ". Error: " + ue.Message);
                }
            }
            else
            {
                if (dp.Language != null)
                {
                    //translate responde
                    IndustrialDashboard.Translate translate = IndustrialDashboard.Translate.Instance;
                    translate.DataSet(ref ds,dp.Language);
                }
                resultObject = ds.ToDictionary();                
            }            
            WebOperationContext.Current.OutgoingResponse.ContentType = "application/json";
            //WebOperationContext.Current.OutgoingResponse.Headers.Add(HttpRequestHeader.AcceptEncoding, "gzip , Deflate");

            /* Send the serialized result to the calling widget  */
            LogHelper.Log.Debug("     -Complete. Serializing Response");
            return new MemoryStream(Encoding.UTF8.GetBytes(jss.Serialize(resultObject)));
        }
        catch (SqlException sqle)
        {
            DALServiceData.ErrorStruct ret = new DALServiceData.ErrorStruct();
            ret.Message = sqle.Message;
            ret.ErrorType = sqle.GetType().Name;
            ret.ErrorNumber = sqle.Number; 
            return new MemoryStream(Encoding.UTF8.GetBytes(jss.Serialize(ret)));
        }
        catch (DataException de)
        {
            LogHelper.Log.Debug("     -Failed. ["+ de.Message+"]");
            LogHelper.Log.Info("     -Failed. [" + de.Message + "]");
            return null;
        }
        catch (UserException ue)
        {
            LogHelper.Log.Debug("     -Failed.");
            LogHelper.Log.Error("Failed.", ue);
            return null;            
        }
        catch (Exception ex)
        {
            LogHelper.Log.Debug("     -Failed.");
            LogHelper.Log.Error("Failed.", ex);

            DALServiceData.ErrorStruct ret = new DALServiceData.ErrorStruct();
            ret.Message = ex.Message;
            ret.ErrorType = ex.GetType().Name;
            return new MemoryStream(Encoding.UTF8.GetBytes(jss.Serialize(ret)));
        }
    }

    /// <summary>
    ///  Execute an arbitrary secured stored procedure, with the provided parameters and returns
    ///  a json response, customized based on widget class, if no class is provided the resulting 
    ///  dataset is serialized as a list of tables.
    /// </summary>
    /// <param name="Parameters">receive a json serialized ProcedureParameters object</param>
    /// <returns></returns>
    public Stream CallSecuredProcedure(Stream Parameters)
    {
        LogHelper.Log.LoggerName = "";
        LogHelper.Log.DisplayMethods = true;
        LogHelper.Log.Info("Initializing.");
        LogHelper.Log.Debug("Initializing.");
        DALServiceData.DataParameters dp;
        JavaScriptSerializer jss = new JavaScriptSerializer();
        jss.MaxJsonLength = 2147483647;
        try
        {
            /* Get input parameters from input stream */
            StreamReader sr = new StreamReader(Parameters);
            dp = jss.Deserialize<DALServiceData.DataParameters>(sr.ReadToEnd());
        }
        catch (FormatException ex)
        {
            LogHelper.Log.Warn("     -FormatException", ex);
            LogHelper.Log.Error("     -FormatException", ex);
            return null;
        }
        try
        {
            if (dp == null)
            {
                LogHelper.Log.Debug("     -Nulls Parameters");
                throw new UserException("Nulls Parameters");
            }
            if (dp.DatabaseParameters == null)
            {
                LogHelper.Log.Debug("     -No DatabaseParameters specified");
                throw new UserException("No DatabaseParameters specified.");
            }
            else if (dp.DatabaseParameters.Procedure == null)
            {
                LogHelper.Log.Debug("     -No Procedure specified");
                throw new UserException("No Procedure specified.");
            }

            String strParameters = "";
            strParameters += "Class : " + dp.Class;
            strParameters += ", Path : " + dp.Path;
            strParameters += ", Namespace : " + dp.Namespace;
            strParameters += ", FileName : " + dp.FileName;
            strParameters += ", Extension : " + dp.Extension;
            strParameters += ", DomainFolder : " + dp.DomainFolder;
            strParameters += ", ConnectionStringName : " + dp.DatabaseParameters.ConnectionStringName;
            strParameters += ", DBEngine : " + dp.DatabaseParameters.DBEngine;
            strParameters += ", Procedure : " + dp.DatabaseParameters.Procedure;
            strParameters += ", TablesCount : " + dp.DatabaseParameters.TablesCount;
            if ((dp.DatabaseParameters != null) && (dp.DatabaseParameters.QueryParameters != null) && (dp.DatabaseParameters.QueryParameters.Count > 0))
            {
                Int32 qpIndex = 1;
                strParameters += ", QueryParameters : {";
                foreach (DALServiceData.QueryParameter qp in dp.DatabaseParameters.QueryParameters)
                {
                    strParameters += "Param : " + qpIndex.ToString() + ", Name: " + qp.Name + ", Value:" + qp.Value + ", Type: " + qp.Type;
                    qpIndex++;
                }
                strParameters += "}";
            }
            LogHelper.Log.Debug("     -Parameters [" + strParameters + "]");


            //LogHelper.Log.Debug("     -CallProcedure. Executing Procedure. (Class: " + dp.Class + ", Procedure: " + dp.DatabaseParameters.Procedure + ")");
            /* Execute Procedure with named and typed parameters */
            DataSet ds = ExecuteSecuredProcedure(dp);
            if (ds == null)
            {
                LogHelper.Log.Debug("     -The dataset is null");
                throw new DataException("The dataset is null");
            }
            Object resultObject = null;
            if (dp.Class != null)
            {
                /* Use Reflection to instatiate a "WidgetType" object and cast it as IWidget */
                Assembly assem = null;
                try
                {
                    String physicalPath = "";
                    if ((dp.Class == null) && (dp.Class == "")) dp.Class = "";
                    if ((dp.DomainFolder == null) || (dp.DomainFolder == "")) dp.DomainFolder = "Widgets\\" + dp.Class + "\\DLL";
                    physicalPath = ((dp.Path != null) && (dp.Path != "")) ? dp.Path : System.Web.Hosting.HostingEnvironment.ApplicationPhysicalPath + dp.DomainFolder + "\\";
                    physicalPath += ((dp.FileName != null) && (dp.FileName != "")) ? dp.FileName : dp.Class;
                    physicalPath += ((dp.Extension != null) && (dp.Extension != "")) ? "." + dp.Extension : ".dll";
                    //to prevent exception, check if the file exist before invoke
                    System.IO.FileInfo fi = new FileInfo(physicalPath);
                    if (fi.Exists == true)
                    {
                        assem = Assembly.LoadFrom(physicalPath);
                    }
                    else
                    {
                        LogHelper.Log.Debug("     -None DLL was found for Class: [" + dp.Class + "], trying to instantiate locally]. ");
                    }
                }
                catch (Exception ex)
                {
                    LogHelper.Log.Debug("     -None DLL was found for Class: [" + dp.Class + "], trying to instantiate locally]. " + ex.Message.ToString());
                    LogHelper.Log.Error("     -None DLL was found for Class: [" + dp.Class + "], trying to instantiate locally]. ", ex);
                }
                if (assem == null)
                {
                    assem = this.GetType().Assembly;
                }
                IWidget iWidgetObject = (IWidget)assem.CreateInstance(dp.Namespace + "." + dp.Class);
                if (iWidgetObject == null)
                {
                    LogHelper.Log.Debug("No declared class was found for Class: [" + dp.Class + "]");
                    throw new Exception("No declared class was found for Class: [" + dp.Class + "]");
                }
                /* Send the DataSet to widget-custom class to convert result */
                try
                {
                    LogHelper.Log.Debug("     -Generating Response");
                    resultObject = iWidgetObject.GenerateResponse(ds, dp.Language);
                }
                catch (UserException ue)
                {
                    LogHelper.Log.Debug("     -Failed. [Problems building the " + dp.Class + "]");
                    LogHelper.Log.Error("     -Failed. [Problems building the " + dp.Class + "]", ue);
                    throw new UserException("Problems building the " + dp.Class + ". Error: " + ue.Message);
                }
            }
            else
            {
                if (dp.Language != null)
                {
                    //translate responde
                    IndustrialDashboard.Translate translate = IndustrialDashboard.Translate.Instance;
                    translate.DataSet(ref ds, dp.Language);
                }
                resultObject = ds.ToDictionary();
            }
            WebOperationContext.Current.OutgoingResponse.ContentType = "application/json";
            //WebOperationContext.Current.OutgoingResponse.Headers.Add(HttpRequestHeader.AcceptEncoding, "gzip , Deflate");

            /* Send the serialized result to the calling widget  */
            LogHelper.Log.Debug("     -Complete. Serializing Response");
            return new MemoryStream(Encoding.UTF8.GetBytes(jss.Serialize(resultObject)));
        }
        catch (SqlException sqle)
        {
            DALServiceData.ErrorStruct ret = new DALServiceData.ErrorStruct();
            ret.Message = sqle.Message;
            ret.ErrorType = sqle.GetType().Name;
            ret.ErrorNumber = sqle.Number;
            return new MemoryStream(Encoding.UTF8.GetBytes(jss.Serialize(ret)));
        }
        catch (DataException de)
        {
            LogHelper.Log.Debug("     -Failed. [" + de.Message + "]");
            LogHelper.Log.Info("     -Failed. [" + de.Message + "]");
            return null;
        }
        catch (UserException ue)
        {
            LogHelper.Log.Debug("     -Failed.");
            LogHelper.Log.Error("Failed.", ue);
            return null;
        }
        catch (Exception ex)
        {
            LogHelper.Log.Debug("     -Failed.");
            LogHelper.Log.Error("Failed.", ex);

            DALServiceData.ErrorStruct ret = new DALServiceData.ErrorStruct();
            ret.Message = ex.Message;
            ret.ErrorType = ex.GetType().Name;
            return new MemoryStream(Encoding.UTF8.GetBytes(jss.Serialize(ret)));
        }
    }

    /// Returns an arbitrary file from the web server, converting its content-typesList to application/json
    public Stream GetFile(Stream Parameters)
    {
        try
        {
            LogHelper.Log.LoggerName = "";
            LogHelper.Log.DisplayMethods = true;
            LogHelper.Log.Info("Initializing GetFile");
            LogHelper.Log.Debug("Initializing GetFile");
            DALServiceData.JSONFileParameters jfp;            
            StreamReader sr = new StreamReader(Parameters);
            JavaScriptSerializer jss = new JavaScriptSerializer();
            jss.MaxJsonLength = 2147483647;
            jfp = jss.Deserialize<DALServiceData.JSONFileParameters>(sr.ReadToEnd());
            if (jfp == null) {
                LogHelper.Log.Debug("     -Nulls Parameters");
                return null; 
            }
            LogHelper.Log.Debug("     -[" + jfp.JSONFile + "]");            

            WebOperationContext.Current.OutgoingResponse.ContentType = "application/json";
            String physicalPath = "";
            if ((jfp.Class == null) && (jfp.Class == "")) jfp.Class = "";
            physicalPath = ((jfp.Path != null) && (jfp.Path != "")) ? jfp.Path : System.Web.Hosting.HostingEnvironment.ApplicationPhysicalPath + "Widgets\\"+jfp.Class+"\\Test\\";
            physicalPath += ((jfp.FileName != null) && (jfp.FileName != "")) ? jfp.FileName : jfp.Class;
            physicalPath += ((jfp.Extension != null) && (jfp.Extension != "")) ? jfp.Extension : ".json"; 
            physicalPath = ((jfp.JSONFile!=null)&&(jfp.JSONFile!=""))?System.Web.Hosting.HostingEnvironment.ApplicationPhysicalPath + jfp.JSONFile:physicalPath;
            LogHelper.Log.Debug("     -Open Path File: [" + physicalPath + "]");
            return new FileStream(physicalPath, FileMode.Open, FileAccess.Read);
        }
        catch (Exception ex)
        {
            LogHelper.Log.Debug("     -Failed.");
            LogHelper.Log.Error("Failed.", ex);
            return null;
        }
    }
    /// <summary>
    /// Export objects to different file types.
    /// </summary>
    /// <returns></returns>
    ///     
    public Stream ExportTo(Stream Parameters)
    {
        try
        {
            LogHelper.Log.LoggerName = "";
            LogHelper.Log.DisplayMethods = true;
            LogHelper.Log.Info("Initializing ExportTo");
            LogHelper.Log.Debug("Initializing ExportTo");
            JavaScriptSerializer jss = new JavaScriptSerializer();
            jss.MaxJsonLength = 2147483647;
            String str = System.Web.HttpUtility.UrlDecode(new StreamReader(Parameters).ReadToEnd().Split('=')[1]);
            DALServiceData.ExportParameters ep = jss.Deserialize<DALServiceData.ExportParameters>(str);            
            //String exporterName = ep.ExportType.ToString();                        

            if (ep.Class != null)
            {
                /* Use Reflection to instatiate a object and cast it as iExport */
                Assembly assem = null;
                try
                {
                    String physicalPath = "";
                    if ((ep.Class == null) && (ep.Class == "")) ep.Class = "";
                    physicalPath = ((ep.Path != null) && (ep.Path != "")) ? ep.Path +"\\": System.Web.Hosting.HostingEnvironment.ApplicationPhysicalPath + "Widgets\\" + ep.Class + "\\DLL\\";
                    physicalPath += ((ep.FileName != null) && (ep.FileName != "")) ? ep.FileName : ep.Class;
                    physicalPath += ((ep.Extension != null) && (ep.Extension != "")) ? "." + ep.Extension : ".dll";
                    System.IO.FileInfo fi = new FileInfo(physicalPath);
                    if (fi.Exists)
                    {
                        assem = Assembly.LoadFrom(physicalPath);
                    }
                    else
                    {
                        LogHelper.Log.Debug("     -None DLL was found for Class: [" + ep.Class + "], trying to instantiate locally]");
                    }
                }
                catch (Exception ex)
                {
                    LogHelper.Log.Debug("     -None DLL was found for Class: [" + ep.Class + "], trying to instantiate locally]" + ex.Message.ToString());
                    LogHelper.Log.Error("     -None DLL was found for Class: [" + ep.Class + "], trying to instantiate locally]",ex);
                }
                if (assem == null) assem = this.GetType().Assembly;

                ExportInterface.IExport IExportObject = (ExportInterface.IExport)assem.CreateInstance(ep.Namespace + "." + ep.Class);
                if (IExportObject == null)
                {
                    LogHelper.Log.Debug("No declared class was found for Class: [" + ep.Class + "]");
                    throw new Exception("No declared class was found for Class: [" + ep.Class + "]");
                }
                /* Send the DataSet to widget-custom class to convert result */
                try
                {
                    LogHelper.Log.Debug("     -Instantiate Class, Exporting...");
                    IExportObject.Export(ep);
                    WebOperationContext.Current.OutgoingResponse.Headers.Add(IExportObject.GetOutgoingResponseHeader(), IExportObject.GetResponseHeaderValue());
                    WebOperationContext.Current.OutgoingResponse.ContentType = IExportObject.GetContentType();                  
                    return IExportObject.GetStream();
                }
                catch (UserException ue)
                {
                    LogHelper.Log.Debug("     -Failed. (Problems building the " + ep.Class + ")");
                    LogHelper.Log.Error("     -Failed. (Problems building the " + ep.Class + ")", ue);
                    throw new UserException("Problems building the " + ep.Class + ". Error: " + ue.Message);
                }
            }
            return null;          
        }
        catch (Exception ex)
        {
            LogHelper.Log.Debug("     -Failed. ");
            LogHelper.Log.Error("Failed", ex);
            return null;
        }
    }
    /// <summary>
    /// Attach Foreign Connection.
    /// Allow to user to add Connection, outside of the web.config file.
    /// </summary>
    /// <returns></returns>
    public Stream AttachConnection(Stream Parameters)
    {
        try
        {
            LogHelper.Log.LoggerName = "";
            LogHelper.Log.DisplayMethods = true;
            LogHelper.Log.Info("Initializing AttachConnection");
            LogHelper.Log.Debug("Initializing AttachConnection");
            ForeignConnection fc;
            StreamReader sr = new StreamReader(Parameters);
            JavaScriptSerializer jss = new JavaScriptSerializer();
            jss.MaxJsonLength = 2147483647;
            fc = jss.Deserialize<ForeignConnection>(sr.ReadToEnd());
            if (fc == null)
            {
                LogHelper.Log.Debug("     -Nulls Parameters");
                return null;
            }
            
            String code;
            if (fc == null) code = "false";
            else
            {
                switch (fc.Action)
                {
                    case ForeignConnection.enumAction.Add:
                        code = DALServiceData.ForeignConnections.AttachConnectionStringName(fc.Name, fc.ConnectionString);
                        break;
                    case ForeignConnection.enumAction.Remove:
                        code = Convert.ToString(DALServiceData.ForeignConnections.DetachConnectionStringName(fc.Name, fc.Code));
                        break;
                    default:
                        code = "false";
                        break;
                }
            }
            if ((code == "-1") || (code == "false")) LogHelper.Log.Debug("     -[Rejected].");
            else LogHelper.Log.Debug("     -[Accepted].");
            WebOperationContext.Current.OutgoingResponse.ContentType = "application/json";
            return new MemoryStream(Encoding.UTF8.GetBytes(jss.Serialize(code)));
        }
        catch (Exception ex)
        {
            LogHelper.Log.Debug("     -Failed.");
            LogHelper.Log.Error("Failed.", ex);
            return null;
        }
    }
    #endregion
    #region "Methods"   
    public DataSet ExecuteProcedure(DALServiceData.DataParameters cp)
    {     
        try
        {
            LogHelper.Log.LoggerName = "";
            LogHelper.Log.DisplayMethods = true;
            LogHelper.Log.Info("     -Initializing ExecuteProcedure");
            LogHelper.Log.Debug("     -Initializing ExecuteProcedure");
            switch (cp.DatabaseParameters.DBEngine.ToUpper())
            {
                case "SQLSERVER":
                    return GetGenericSQLDataAdapter(cp);
                case "ORACLE":
                    Type thisType = this.GetType();
                    MethodInfo handler = thisType.GetMethod("GetGenericSQLDataAdapterForOracle");
                    if (handler != null)
                    {
                        object[] handlerParams = new object[1];
                        handlerParams[0] = cp;
                        return (DataSet)handler.Invoke(this, handlerParams);
                    }
                    else
                    {
                        LogHelper.Log.Debug("          -Problems executing procedure: " + cp.DatabaseParameters.Procedure + ", Industrial Dashboard is not configure it to support Oracle connections.");
                        throw new UserException("ExecuteProcedure. Problems executing procedure" + cp.DatabaseParameters.Procedure + ". Error: Industrial Dashboard is not configure it to support Oracle connections.");
                    }
                default:
                    {
                        LogHelper.Log.Debug("          -Problems executing procedure: " + cp.DatabaseParameters.Procedure + ", Invalid 'Connection String Name'.");
                        throw new UserException("ExecuteProcedure. Problems executing procedure" + cp.DatabaseParameters.Procedure + ". Error: Invalid 'Connection String Name'.");
                    }
            }
        }
        catch (Exception ex)
        {
            LogHelper.Log.Debug("          -Failed. [Message : " + ex.Message+"]");
            LogHelper.Log.Error("ExecuteProcedure Failed.", ex);
            throw ex;
        }
    }
    public DataSet ExecuteSecuredProcedure(DALServiceData.DataParameters cp)
    {
        try
        {
            LogHelper.Log.LoggerName = "";
            LogHelper.Log.DisplayMethods = true;
            LogHelper.Log.Info("     -Initializing ExecuteSecuredProcedure");
            LogHelper.Log.Debug("     -Initializing ExecuteSecuredProcedure");
            switch (cp.DatabaseParameters.DBEngine.ToUpper())
            {
                case "SQLSERVER":
                    SqlConnection conn = GetUserSQLConnection(cp);
                    SqlCommand comm = GetDataParametersSQLCommand(cp);

                    return Security.DALSecurity.CallSecuredProcedure(conn, comm); 
                case "ORACLE":
                default:
                    {
                        LogHelper.Log.Debug("          -Problems executing secured procedure: " + cp.DatabaseParameters.Procedure + ", Invalid 'Connection String Name'.");
                        throw new UserException("ExecuteProcedure. Problems executing secured procedure" + cp.DatabaseParameters.Procedure + ". Error: Invalid 'Connection String Name'.");
                    }
            }
        }
        catch (Exception ex)
        {
            LogHelper.Log.Debug("          -Failed. [Message : " + ex.Message + "]");
            LogHelper.Log.Error("ExecuteSecuredProcedure Failed.", ex);
            throw ex;
        }
    }
    private DataSet GetGenericSQLDataAdapter(DALServiceData.DataParameters cp)
    {
        try
        {
            LogHelper.Log.LoggerName = "";
            LogHelper.Log.Info("          -Initializing GetGenericSQLDataAdapter");
            LogHelper.Log.Debug("          -Initializing GetGenericSQLDataAdapter");

            SqlConnection sqlCon = new SqlConnection();
            
            DALServiceData.UserConnections uconn = GetLoggedUser(); 
            sqlCon = GetUserSQLConnection(cp, uconn); 

            LogHelper.Log.Debug("               -Open SqlConnection [UserName: " + uconn.UserName + ", StoredProcedure: " + cp.DatabaseParameters.Procedure + ", ConnectionName: " + cp.DatabaseParameters.ConnectionStringName + "]");
            sqlCon.Open();

            SqlCommand comm = GetDataParametersSQLCommand(cp);
            comm.Connection = sqlCon; 

            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter(comm);
            da.Fill(ds);
            da.Dispose();
            sqlCon.Close();
            return ds;
        }
        catch (SqlException sqle)
        {
            LogHelper.Log.Debug("               -SQL Operation failed.");
            LogHelper.Log.Error("GetGenericSQLDataAdapter. SQL Operation failed.", sqle);
            throw sqle;
        }
        catch (Exception ex)
        {
            LogHelper.Log.Debug("               -Failed. [Message: " + ex.Message + "]");
            LogHelper.Log.Error("GetGenericSQLDataAdapter. Failed.", ex);
            throw ex;
        }
    }
    private SqlConnection GetUserSQLConnection(DALServiceData.DataParameters cp, DALServiceData.UserConnections uconn = null)
    {
        try
        {
            LogHelper.Log.LoggerName = "";
            LogHelper.Log.Info("          -Initializing GetLoggedUserSQLConnection");
            LogHelper.Log.Debug("          -Initializing GetLoggedUserSQLConnection");

            SqlConnection sqlCon = new SqlConnection();
            sqlCon.ConnectionString = null;
            uconn = (uconn == null) ? GetLoggedUser() : uconn;
            if (uconn == null)
            {
                LogHelper.Log.Debug("               -GetUser [NULL]");
                sqlCon.ConnectionString = GetConnectionString(null, null, "DEFAULT");
            }
            else
            {
                uconn.LastContact = uconn.Logged = DateTime.UtcNow;
                uconn.IPAddress = LogHelper.HostRequest.GetIPAddress();
                UserConnections.Update(uconn);
                if (uconn.SecurityData.ContainsKey(cp.DatabaseParameters.ConnectionStringName))
                {
                    sqlCon.ConnectionString = uconn.SecurityData[cp.DatabaseParameters.ConnectionStringName].ConnectionString;//GetConnectionString(iUser.UserName,iUser.Password,cp.DatabaseParameters.ConnectionStringName);          
                }
            }
            if ((sqlCon.ConnectionString == null) || (sqlCon.ConnectionString == ""))
            {
                LogHelper.Log.Debug("               -Failed. [ConnectionString can not be null]");
                //Exception ex = new Exception("ConnectionString can not be null");
                //LogHelper.Log.Error("               -GetGenericSQLDataAdapter Failed. [ConnectionString can not be null]", ex);
                //throw ex;
                return null;
            }

            return sqlCon; 
        }
        catch (Exception ex)
        {
            LogHelper.Log.Debug("          -Failed.");
            LogHelper.Log.Error("Failed.", ex);
            throw new Exception("GetLoggedUserSQLConnection Failed.", ex);
        }
    }
    private SqlCommand GetDataParametersSQLCommand(DALServiceData.DataParameters cp)
    {
        try
        {
            SqlCommand comm = new SqlCommand(cp.DatabaseParameters.Procedure);
            comm.CommandType = CommandType.StoredProcedure;

            if (cp.DatabaseParameters.QueryParameters != null)
            {
                //cp.DatabaseParameters.QueryParameters
                foreach (DALServiceData.QueryParameter param in cp.DatabaseParameters.QueryParameters)
                {
                    SqlParameter sqlParam = new SqlParameter(param.Name, (SqlDbType)param.Type);
                    if ((SqlDbType)param.Type == SqlDbType.DateTime)
                    {
                        if ((param.Value == null) || (param.Value.ToString() == "")) sqlParam.Value = null;
                        else sqlParam.Value = DateTime.Parse((String)param.Value, CultureInfo.GetCultureInfo("en-US"));
                    }
                    else
                    {
                        sqlParam.Value = param.Value;
                    }
                    comm.Parameters.Add(sqlParam);
                }
            }

            return comm; 
        }
        catch (Exception ex)
        {
            LogHelper.Log.Debug("          -Failed.");
            LogHelper.Log.Error("Failed.", ex);
            throw new Exception("GetDataParametersSQLCommand Failed.", ex);
        }
    }
    private String GetConnectionString(String UserID, String Password, String ConnectionName)
    {
        try
        {
            LogHelper.Log.LoggerName = "";
            LogHelper.Log.Info("     -Initializing GetConnectionString [UserID: "+UserID + ", ConnectionName: " + ConnectionName +"]");
            LogHelper.Log.Debug("     -Initializing GetConnectionString [UserID: " + UserID + ", ConnectionName: " + ConnectionName + "]");

            System.Text.RegularExpressions.Regex regexp;
            String ConnectionString=null;
            if (ConnectionName.ToUpper() == "LOCALSQLSERVER") return null;
            else if ((ConnectionName == null) || (ConnectionName == "") || (ConnectionName.ToUpper() == "DEFAULT"))
            {
                LogHelper.Log.Debug("          -GetConnectionString. Using Default Connection.");                
                foreach (System.Configuration.ConnectionStringSettings css in ConfigurationManager.ConnectionStrings)
                {
                    if ((css.Name != "LocalSqlServer") && (css.ConnectionString.ToString().Trim() != "")) ConnectionString = css.ConnectionString.ToString();                    
                }                
            }
            if ((ConfigurationManager.ConnectionStrings[ConnectionName]!=null) &&(ConnectionString==null))
            {
                LogHelper.Log.Debug("          -GetConnectionString. Using Web.config Connection.");
                ConnectionString = ConfigurationManager.ConnectionStrings[ConnectionName].ToString();
            }
            else if ((DALServiceData.ForeignConnections.Contains(ConnectionName))&&(ConnectionString==null)){
                LogHelper.Log.Debug("          -GetConnectionString. Using Attach Connection.");
                ConnectionString = DALServiceData.ForeignConnections.GetConnectionString(ConnectionName);
            }
            //SSPI.
            //regexp = new System.Text.RegularExpressions.Regex ("[Ii][Nn][Tt][Ee][Gg][Rr][Aa][Tt][Ee][Dd] [Ss][Ee][Cc][Uu][Rr][Ii][Tt][Yy]*=*[A-Z,a-z]*;");
            String regexDataSource = "Integrated\\sSecurity(\\s)*=(\\s)*(?<IntegratedSecurity>([^;]*))";
            System.Text.RegularExpressions.RegexOptions options;
            regexp = new System.Text.RegularExpressions.Regex(regexDataSource);//, options);            
            if (regexp.Match(ConnectionString).Success)
            {
                ConnectionString = regexp.Replace(ConnectionString,"");
            }
            if ((UserID != null) && (UserID != ""))
            {
                regexDataSource = "User\\sID(\\s)*=(\\s)*(?<UserID>([^;]*))";
                options = System.Text.RegularExpressions.RegexOptions.IgnorePatternWhitespace | System.Text.RegularExpressions.RegexOptions.Multiline | System.Text.RegularExpressions.RegexOptions.IgnoreCase | System.Text.RegularExpressions.RegexOptions.Compiled;
                regexp = new System.Text.RegularExpressions.Regex(regexDataSource, options);
                if (regexp.Match(ConnectionString).Success)
                {
                    ConnectionString = regexp.Replace(ConnectionString, "User ID=" + UserID);
                }
                else
                {
                    ConnectionString += "User ID = " + UserID + ";";
                }

                regexDataSource = "Password(\\s)*=(\\s)*(?<Password>([^;]*))";
                options = System.Text.RegularExpressions.RegexOptions.IgnorePatternWhitespace | System.Text.RegularExpressions.RegexOptions.Multiline | System.Text.RegularExpressions.RegexOptions.IgnoreCase | System.Text.RegularExpressions.RegexOptions.Compiled;
                regexp = new System.Text.RegularExpressions.Regex(regexDataSource, options);
                if (regexp.Match(ConnectionString).Success)
                {
                    ConnectionString = regexp.Replace(ConnectionString, "Password=" + Password);
                }
                else
                {
                    ConnectionString += "Password = " + Password + ";";
                }

                //regexp = new System.Text.RegularExpressions.Regex("[Uu][Ss][Ee][Rr] [Ii][Dd]=[A-Z,a-z,0-9 ]*[*]*");
                //if (regexp.Match(ConnectionString).Success) ConnectionString = regexp.Replace(ConnectionString, "User ID=" + UserID);
                //else ConnectionString += "User ID=" + UserID + ";";

                //regexp = new System.Text.RegularExpressions.Regex("[Pp][Aa][Ss]{2}[Ww][Oo][Rr][Dd]*=*[A-Z,a-z,0-9,@-_ ]*[*]*");
                //if (regexp.Match(ConnectionString).Success) ConnectionString = regexp.Replace(ConnectionString, "Password=" + Password);
                //else ConnectionString += "Password = " + Password + ";"; 
            }
            //Connect Timeout=5;

            //regexp = new System.Text.RegularExpressions.Regex("[Cc][Oo][Nn]{2}[Ee][Cc][Tt] [Tt][Ii][Mm][Ee][Oo][Uu][Tt]*=*[0-9]*");
            //if (!regexp.Match(ConnectionString).Success)
            //{
            //    ConnectionString += "Connect Timeout=5;";
            //}

            regexDataSource = "Connect\\sTimeOut(\\s)*=(\\s)*(?<ConnectTimeOut>([^;]*))";
            options = System.Text.RegularExpressions.RegexOptions.IgnorePatternWhitespace | System.Text.RegularExpressions.RegexOptions.Multiline | System.Text.RegularExpressions.RegexOptions.IgnoreCase | System.Text.RegularExpressions.RegexOptions.Compiled;
            regexp = new System.Text.RegularExpressions.Regex(regexDataSource, options);
            if (!regexp.Match(ConnectionString).Success)
            {
                ConnectionString += "Connect Timeout=5;"; 
            }           

            //LogHelper.Log.Debug("          -GetConnectionString Return [ConnectionName: " + ConnectionName + ", ConnectionString: + " + ConnectionString + ", UserID: " + UserID + "]");
            return ConnectionString;
        }
        catch (Exception ex)
        {
            LogHelper.Log.Debug("     -GetConnectionString Failed [UserID: + " + UserID + ", ConnectionName: " + ConnectionName + "]");
            LogHelper.Log.Error("GetConnectionString Failed [UserID: + " + UserID + ", ConnectionName: " + ConnectionName + "]",ex);
            throw new Exception("GetConnectionString Failed [UserID: + " + UserID + ", ConnectionName: " + ConnectionName + "]", ex);
        }
    }
    private static String GetUserStringLog(DALServiceData.UserConnections uc)
    {
        try
        {
            String str = "";
            String regexDataSource = "Password(\\s)*=(\\s)*(?<Password>([^;]*))";
            System.Text.RegularExpressions.RegexOptions options = System.Text.RegularExpressions.RegexOptions.IgnorePatternWhitespace | System.Text.RegularExpressions.RegexOptions.Multiline | System.Text.RegularExpressions.RegexOptions.IgnoreCase | System.Text.RegularExpressions.RegexOptions.Compiled;
            System.Text.RegularExpressions.Regex regexp = new System.Text.RegularExpressions.Regex(regexDataSource, options);

            if (uc != null)
            {
                str += "UserName: " + uc.UserName;
                str += ", IPAddress: " + uc.IPAddress;
                str += ", Key: " + uc.Key;
                str += ", LastContact: " + uc.LastContact.ToString();
                str += ", Logged: " + uc.Logged.ToString();
                str += ", Security Data: {";
                foreach (KeyValuePair<String, DALServiceData.LocalSecurityData> d in uc.SecurityData)
                {                    
                    //System.Text.RegularExpressions.Regex regexp = new System.Text.RegularExpressions.Regex("[Pp][Aa][Ss]{2}[Ww][Oo][Rr][Dd]*=*[A-Z,a-z,0-9 ]*[*]*");
                    //if (regexp.Match(d.Value.ConnectionString.ToString()).Success) str += "ConnectionString: " +regexp.Replace(d.Value.ConnectionString.ToString(), "Password=*********");
                    //else str += "ConnectionString: " + d.Value.ConnectionString.ToString(); 

                    if (regexp.Match(d.Value.ConnectionString.ToString()).Success)
                    {
                        str += "ConnectionString: " + regexp.Replace(d.Value.ConnectionString.ToString(), "Password=*********");
                    }
                    else
                    {
                        str += "ConnectionString: " + d.Value.ConnectionString.ToString(); 
                    }


                    //str += "ConnectionString: " + d.Value.ConnectionString.ToString();
                    str += " Roles: {";
                    foreach (String role in d.Value.Roles)
                    {
                        str += role.ToString() +", ";
                    }
                    str = str.Remove(str.Length - 2, 1);
                    str += "}, ExtendedProperties: {";
                    foreach (KeyValuePair<String, String> ext in d.Value.ExtendedProperties)
                    {
                        str += ext.Value.ToString() + ", ";                        
                    }
                    str = str.Remove(str.Length - 2, 1);
                    str += "}, ";
                }
                str = str.Remove(str.Length - 2, 1);
                str += "}";
            }
            return str;
        }
        catch (Exception ex)
        {
            LogHelper.Log.Debug("     -GetUserStringLog. Failed [Message: " + ex.Message + "]");
            LogHelper.Log.Error("     -GetUserStringLog. Failed", ex);
            return "";
        }
    }
    public DALServiceData.UserConnections GetLoggedUser()
    {
        LogHelper.Log.LoggerName = "";
        JavaScriptSerializer jss = new JavaScriptSerializer();
        jss.MaxJsonLength = 2147483647;
        LogHelper.Log.Info("     -Initializing GetLoggedUser");
        LogHelper.Log.Debug("     -Initializing GetLoggedUser");
        try
        {
            /* Get input parameters from input stream */
            LogHelper.Log.Debug("          -Get from Incoming Request");
            Object cookie = WebOperationContext.Current.IncomingRequest.Headers[HttpRequestHeader.Cookie];
            String NameID = "SessionID";
            if (cookie != null)
            {
                String[] str = cookie.ToString().Split(';');
                for (Int32 i = 0; i < str.Length; i++)
                {
                    String s = str[i].Trim();
                    if ((s.Length >= NameID.Length) && (s.Substring(0, NameID.Length) == NameID))
                    {
                        String[] Attributes = s.Split(',');                        
                        String Key = "";
                        DateTime ExpireDate = DateTime.UtcNow.AddMinutes(-1);
                        for (Int32 k = 0; k < Attributes.Length; k++)
                        {
                            String[] Attribute = Attributes[k].Split('=');
                            switch(Attribute[0].ToUpper().Trim()){
                                case "SESSIONID":
                                    Key = Attribute[1];
                                    break;
                                case "EXPIRES":
                                    if ((Key!="")&&(UserConnections.GetUserConnections(Key)!=null)){
                                        ExpireDate = Convert.ToDateTime(Attribute[1]);                                   
                                    }
                                    break;
                            }                                                  
                        }
                        //LogHelper.Log.Debug("Expires Time: " + ExpireDate.ToString() + ", Local Time: "+DateTime.UtcNow.ToString());
                        DALServiceData.UserConnections iu = UserConnections.GetUserConnections(Key);
                        if ((Key != "") && (ExpireDate > DateTime.UtcNow))
                        {                           
                            if (iu != null)
                            {
                                iu.LastContact = DateTime.UtcNow;
                                String strUser = GetUserStringLog(iu);
                                LogHelper.Log.Debug("          -Return [" + strUser + "]");
                                String strCookie = GetClientCookie(Key, DateTime.UtcNow.AddMinutes(ExpiresMinutesTime));
                                WebOperationContext.Current.OutgoingResponse.Headers.Remove("Set-Cookie");
                                WebOperationContext.Current.OutgoingResponse.Headers[HttpResponseHeader.SetCookie] = strCookie;
                                return iu;
                            }
                        }
                        else if ((Key != "") && (ExpireDate < DateTime.UtcNow))
                        {
                            if (iu != null)
                            {
                                LogHelper.Log.Debug("          -Session has expired [" + iu.UserName + "]");
                                //remove SessionID from Dictionary
                                UserConnections.Remove(Key);
                            }
                            else
                            {

                                LogHelper.Log.Debug("          -Session has expired.");
                            }
                        }
                    }
                }
            }
            LogHelper.Log.Debug("          -Check if a user exists by (default or public) user.");
            DALServiceData.UserConnections uc = UserConnections.GetUserConnections("NULL");
            if (uc == null)
            {
                LogHelper.Log.Debug("          -New Login Connection.");               
                uc = GetDefaultUserData();
                LoginUser("NULL", "", false,uc);
                return uc;               
            }
            else
            {
                return uc;
            }           
        }
        catch (Exception ex)
        {
            LogHelper.Log.Debug("          -Failed.");
            LogHelper.Log.Error("Failed.", ex);
            throw new Exception("GetLoggedUser failed.", ex);
        }
    }
    private DALServiceData.UserConnections GetDefaultUserData()
    {
        try
        {
            LogHelper.Log.LoggerName = "";
            LogHelper.Log.Info("     -Initializing GetDefaultUserData ");
            LogHelper.Log.Debug("     -Initializing GetDefaultUserData ");
            ///<summary>
            /// Return columns
            /// [Name],[RoleName],[ExtendedPropertyName],[ExtendedPropertyValue]
            ///</summary>
            DALServiceData.UserConnections uconn = null;
            foreach (System.Configuration.ConnectionStringSettings connectionSettings in ConfigurationManager.ConnectionStrings)
            {
                if ((connectionSettings.ConnectionString.ToString().Trim() != "") && (connectionSettings.Name != "LocalSqlServer"))
                {
                    String ConnectionString = connectionSettings.ConnectionString;
                    if (ConnectionString != null)
                    {
                        String UserName = "";
                        //System.Text.RegularExpressions.Regex regexp = new System.Text.RegularExpressions.Regex("[Uu][Ss][Ee][Rr] [Ii][Dd]=[A-Z,a-z,0-9 ]*[*]*");
                        //if (regexp.Match(ConnectionString).Success)
                        //{
                        //    System.Text.RegularExpressions.MatchCollection mc = regexp.Matches(ConnectionString);
                        //    if (mc.Count > 0) UserName = mc[0].ToString().Split('=')[1];
                        //}
                        String regexDataSource = "User\\sID(\\s)*=(\\s)*(?<UserID>([^;]*))";
                        System.Text.RegularExpressions.RegexOptions options = System.Text.RegularExpressions.RegexOptions.IgnorePatternWhitespace | System.Text.RegularExpressions.RegexOptions.Multiline | System.Text.RegularExpressions.RegexOptions.IgnoreCase | System.Text.RegularExpressions.RegexOptions.Compiled;
                        System.Text.RegularExpressions.Regex regexp = new System.Text.RegularExpressions.Regex(regexDataSource, options);
                        if (regexp.Match(ConnectionString).Success)
                        {
                            System.Text.RegularExpressions.MatchCollection mc = regexp.Matches(ConnectionString);
                            if (mc.Count > 0) UserName = mc[0].ToString().Split('=')[1];
                        }
                        DataSet ds = UserExtendedProperties.UserExtendedProperties.GetUserExtendedProperties(UserName, ConnectionString);
                        if ((ds != null) && (ds.Tables.Count > 0))
                        {
                            DALServiceData.LocalSecurityData conn = new DALServiceData.LocalSecurityData();
                            conn.ConnectionString = ConnectionString;
                            Boolean IsValidUser = false;
                            foreach (DataRow r in ds.Tables[0].Rows)
                            {
                                //Name,RoleName,ExtendedPropertyName,ExtendedPropertyValue
                                String RoleName = r["Name"].ToString().Trim();
                                conn.Roles.Add(RoleName);
                                String extPropName = r["ExtendedPropertyName"].ToString().Trim();
                                String extPropValue = r["ExtendedPropertyValue"].ToString();
                                if ((extPropName.ToUpper() == "IDUSER") && (Convert.ToBoolean(extPropValue) == true)) IsValidUser = true;
                                else if ((extPropName != "") && (!conn.ExtendedProperties.ContainsKey(extPropName))) conn.ExtendedProperties.Add(extPropName, extPropValue);
                            }
                            if (IsValidUser)
                            {
                                LogHelper.Log.Debug("          -Valid Connection: [" + connectionSettings.Name + "]");
                                if (uconn == null) uconn = new DALServiceData.UserConnections();
                                uconn.SecurityData.Add(connectionSettings.Name, conn);
                            }
                        }
                    }
                }
            }
            if (uconn != null)
            {
                uconn.UserName = "NULL";
                uconn.Password = "";
            }
            String str = GetUserStringLog(uconn);
            if (uconn == null) LogHelper.Log.Debug("          -Return (NULL)");
            else LogHelper.Log.Debug("          -Return [" + str + "]");
            return uconn;
        }
        catch (Exception ex)
        {
            LogHelper.Log.Debug("          -Failed.");
            LogHelper.Log.Error("Failed.", ex);
            throw new Exception("GetDefaultUserData Failed.", ex);
        }
    }
    private DALServiceData.UserConnections GetUserData(String UserName, String Password)
    {
        try
        {
            LogHelper.Log.LoggerName = "";
            LogHelper.Log.Info("     -Initializing GetUserData [UserName: "+ UserName +"]");
            LogHelper.Log.Debug("     -Initializing GetUserData [UserName: " + UserName + "]");
            ///<summary>
            /// Return columns
            /// [Name],[RoleName],[ExtendedPropertyName],[ExtendedPropertyValue]
            ///</summary>
            DALServiceData.UserConnections uconn = null;
            foreach (System.Configuration.ConnectionStringSettings connectionSettings in ConfigurationManager.ConnectionStrings)
            {
                String ConnectionString = GetConnectionString(UserName, Password, connectionSettings.Name);
                if (ConnectionString != null)
                {
                    //LogHelper.Log.Debug("************** UserName: " + UserName + " ConnectionString: " + ConnectionString);
                    DataSet ds = UserExtendedProperties.UserExtendedProperties.GetUserExtendedProperties(UserName, ConnectionString);
                    if ((ds != null) && (ds.Tables.Count > 0))
                    {
                        DALServiceData.LocalSecurityData conn = new DALServiceData.LocalSecurityData();                        
                        conn.ConnectionString = ConnectionString;
                        Boolean IsValidUser = false;
                        foreach (DataRow r in ds.Tables[0].Rows)
                        {
                            //Name,RoleName,ExtendedPropertyName,ExtendedPropertyValue
                            String RoleName = r["Name"].ToString().Trim();
                            conn.Roles.Add(RoleName);
                            String extPropName = r["ExtendedPropertyName"].ToString().Trim();
                            String extPropValue = r["ExtendedPropertyValue"].ToString();
                            if ((extPropName.ToUpper() == "IDUSER") && (Convert.ToBoolean(extPropValue) == true)) IsValidUser = true;
                            else if ((extPropName != "") && (!conn.ExtendedProperties.ContainsKey(extPropName))) conn.ExtendedProperties.Add(extPropName, extPropValue);
                        }
                        if (IsValidUser)
                        {
                            LogHelper.Log.Debug("          -Valid Connection: [" + connectionSettings.Name + "]");                                
                            if (uconn == null) uconn = new DALServiceData.UserConnections();
                            uconn.SecurityData.Add(connectionSettings.Name, conn);
                        }
                    }                   
                }               
            }
            if (uconn != null)
            {
                uconn.UserName = UserName;
                uconn.Password = Password;
            }            
            String str = GetUserStringLog(uconn);
            if (uconn == null) LogHelper.Log.Debug("          -Return (NULL)");
            else LogHelper.Log.Debug("          -Return [" + str + "]");
            return uconn;
        }
        catch (Exception ex)
        {
            LogHelper.Log.Debug("          -Failed. [UserName: " + UserName + "]");
            LogHelper.Log.Error("Failed. [UserName: " + UserName + "]", ex);
            throw new Exception("GetUserData Failed. [UserName: " + UserName + "]", ex);
        }
    }   
    #endregion
}